PostGIS教程八:关于几何图形的练习 | 您所在的位置:网站首页 › 纽约west village › PostGIS教程八:关于几何图形的练习 |
一、函数列表
以下是我们迄今为止看到的所有函数的提示,它们应该对练习有用! sum(expression) aggregate to return a sum for a set of recordscount(expression) aggregate to return the size of a set of recordsST_GeometryType(geometry) returns the type of the geometryST_NDims(geometry) returns the number of dimensions of the geometryST_SRID(geometry) returns the spatial reference identifier number of the geometryST_X(point) returns the X ordinateST_Y(point) returns the Y ordinateST_Length(linestring) returns the length of the linestringST_StartPoint(geometry) returns the first coordinate as a pointST_EndPoint(geometry) returns the last coordinate as a pointST_NPoints(geometry) returns the number of coordinates in the linestringST_Area(geometry) returns the area of the polygonsST_NRings(geometry) returns the number of rings (usually 1, more if there are holes)ST_ExteriorRing(polygon) returns the outer ring as a linestringST_InteriorRingN(polygon, integer) returns a specified interior ring as a linestringST_Perimeter(geometry) returns the length of all the ringsST_NumGeometries(multi/geomcollection) returns the number of parts in the collectionST_GeometryN(geometry, integer) returns the specified part of the collectionST_GeomFromText(text) returns geometryST_AsText(geometry) returns WKT textST_AsEWKT(geometry) returns EWKT textST_GeomFromWKB(bytea) returns geometryST_AsBinary(geometry) returns WKB byteaST_AsEWKB(geometry) returns EWKB byteaST_GeomFromGML(text) returns geometryST_AsGML(geometry) returns GML textST_GeomFromKML(text) returns geometryST_AsKML(geometry) returns KML textST_AsGeoJSON(geometry) returns JSON textST_AsSVG(geometry) returns SVG text还有请记住我们现在数据库中已经有的表: nyc_census_blocks blkid, popn_total, boroname, geomnyc_streets name, type, geomnyc_subway_stations name, geomnyc_neighborhoods name, boroname, geom 二、练习①'West Village'社区(neighborhood)的面积是多少? SELECT ST_Area(geom) FROM nyc_neighborhoods WHERE name = 'West Village'; 注意:面积以平方米为单位。要得到一个以公顷为单位的面积,需要再对其除以10000;要得到一个以英亩为单位的面积,需要对其除以4047。 ②曼哈顿(Manhattan)行政区的面积是多少英亩?(提示:nyc_census_blocks和nyc_neighborhoods中都有boroname - rorough name - 行政区名) SELECT Sum(ST_Area(geom)) / 4047 FROM nyc_neighborhoods WHERE boroname = 'Manhattan'; 或者: SELECT Sum(ST_Area(geom)) / 4047 FROM nyc_census_blocks WHERE boroname = 'Manhattan'; ③纽约市(New York City)有多少个人口普查块(census blocks)多边形里有孔洞? SELECT Count(*) FROM nyc_census_blocks WHERE ST_NumInteriorRings(ST_GeometryN(geom,1)) > 0; 注意:ST_NRings()函数可能让人感觉可以胜任,但是它也计算多-多边形的外环和内环。为了运行ST_NumInteriorRings(),我们需要将MultiPolygon几何图形转换为简单的多边形,因此,我们使用ST_GeometryN()从每个集合中提取第一个多边形。 ④纽约市(New York)的街道总长度(公里)是多少?(提示:空间数据的测量单位是米,每公里有1000米) SELECT Sum(ST_Length(geom)) / 1000 FROM nyc_streets; ⑤'Columbus Cir'(哥伦布圆环——纽约曼哈顿区的一个地标)有多长? SELECT ST_Length(geom) FROM nyc_streets WHERE name = 'Columbus Cir'; ⑥West Village社区边界的JSON表示是怎样的? SELECT ST_AsGeoJSON(geom) FROM nyc_neighborhoods WHERE name = 'West Village'; 返回的JSON里的几何类型是"MultiPolygon(多多边形)",有趣! ⑦West Village社区多多边形(MultiPolygon)中有多少个多边形? SELECT ST_NumGeometries(geom) FROM nyc_neighborhoods WHERE name = 'West Village'; 注意:在空间表中找到单元素多多边形并不少见。使用多多边形允许只有一种几何图形类型的表同时存储单(single-)几何图形和多(multi-)几何图形,而不必使用GeometryCollection类型。 ⑧按类型(type)列出纽约市街道长度是多少? SELECT type, Sum(ST_Length(geom)) AS length FROM nyc_streets GROUP BY type ORDER BY length DESC; 注意:ORDER BY length DESC子句按长度以降序形式排序。 |
CopyRight 2018-2019 实验室设备网 版权所有 |